package com.tledu.springboot19.dao.impl;

import com.tledu.springboot19.dao.NewsDao;
import com.tledu.springboot19.entity.News;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class NewsDaoImpl implements NewsDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public void save(News news) {
        String sql = "insert into news (url, source, title, send_time, category, content, content_html) VALUE (?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, news.getUrl(), news.getSource(), news.getTitle(),
                news.getSendTime(), news.getCategory(), news.getContent(), news.getContentHtml());
    }

    @Override
    public boolean exist(String url) {
        // 统计一下是否有存在的url就可以了
        String sql = "select count(*) from news where url = ?";
        Integer num = jdbcTemplate.queryForObject(sql, Integer.class,url);
        return num != null && num > 0;
    }

    @Override
    public List<News> newsList(String title) {
        String sql = "select * from news where title like concat('%',?,'%') order by send_time desc limit 10";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, title);
        List<News> newsList = new ArrayList<>();
        for (Map<String, Object> item : list) {
            News news = new News();
            news.setSource((String) item.get("source"));
            news.setId((Integer) item.get("id"));
            news.setTitle((String) item.get("title"));
            news.setContentHtml((String) item.get("content_html"));
            news.setContent((String) item.get("content"));
            news.setCategory((String) item.get("category"));

            // 日期类型转换
            Instant instant =  ((LocalDateTime)item.get("send_time")).atZone(ZoneId.systemDefault()).toInstant();
            Date date = Date.from(instant);
            news.setSendTime(date);

            news.setUrl((String) item.get("url"));
            newsList.add(news);
        }
        return newsList;
    }

    @Override
    public List<Map<String, Object>> countCategory() {
        String sql = "select category, count(*) num from news " +
                "where category is not null and category != '' " +
                "group by category " +
                "order by num desc " +
                "limit 7";
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> countByDate() {
        String sql = "select date_format(send_time,'%Y-%m-%d') date , count(*) num from news group by date order by date desc limit 7;";
        return jdbcTemplate.queryForList(sql);
    }
}
